package cn.edu.swu.dlx;
import java.util.Date;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcTest {

    String url = "jdbc:mysql://10.122.7.154:3306/book_store_2025";
    String username = "root";
    String password = "qwer1234";

    public static void main(String[] args) {
        JdbcTest test=new JdbcTest();
        String insertsql= """
                insert into book(name,author,price,publish)value
                ('%s','%s','%s','%s')""";
        test.doUpdate(String.format(
                insertsql,
                "Java程序设计",
                "张三",
                59.99,
                20230101
        ));

        test.doUpdate(String.format(
                insertsql,
                "数据库原理",
                "李四",
                69.99,
                20230201
        ));

        String querysql="select * from book";
        List<Book> books=test.doQuery(querysql);
        test.printBooks(books);
    }

    public List<Book> doQuery(String sql) {
        try(Connection connection= DriverManager.getConnection(url, username, password)){
            try(Statement statement=connection.createStatement()){
                try(ResultSet resultSet=statement.executeQuery(sql)){
                    List<Book> books=new ArrayList<>();
                    while(resultSet.next()){
                        int id=resultSet.getInt("id");
                        String name=resultSet.getString("name");
                        String author=resultSet.getString("author");
                        double price=resultSet.getDouble("price");
                        Date publishDate=resultSet.getDate("publish");
                        Book book=new Book(id,name,author,price,publishDate);
                        books.add(book);
                    }
                    return books;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public boolean doUpdate(String sql) {
        try(Connection connection= DriverManager.getConnection(url, username, password)){
            try(Statement statement=connection.createStatement()){
                return statement.execute(sql);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void printBooks(List<Book> books) {
        for(Book book:books) {
            System.out.println(book);
        }
    }
}
